/**
 * 
 */
package extract.db;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.StringTokenizer;

import extract.file.processing.GutFile;
/**
 * @author chris
 *
 */
public class DB {
    
    Connection con;
    
    public DB()
    {
	String dbUrl = "jdbc:mysql://localhost/extract_search";
	String dbClass = "com.mysql.jdbc.Driver";
	String username = "root";
	String password = "dorka";
	
	try 
	{

	    Class.forName("com.mysql.jdbc.Driver");
	    con = DriverManager.getConnection (dbUrl, username, password);
	    Statement stmt = con.createStatement();

	}

	catch(ClassNotFoundException e) 
	{
	    e.printStackTrace();
	}

	catch(SQLException e) 
	{
	    e.printStackTrace();
	}
    }
    
    public void insertData() throws SQLException, IOException
    {
	BufferedReader reader = new BufferedReader(new FileReader("/home/chris/workspace/trimmedgut/modified/12969.txt.utf8.trim"));
	String input = null;
	int length;
	String x;
	while((input = reader.readLine()) != null)
	{
	    StringTokenizer t = new StringTokenizer(input, "###");
	    input = t.nextToken();
	    if(input.length() > 100)
	    {
	     x = input.substring(0, 99);
	    }
	    else
	    {
		x = input;
	    }
	    length = Integer.parseInt(t.nextToken());
	    String sql = "INSERT INTO extracts (asl,title,start_point,extract_length) VALUES(?,?,?,?)";  
	    PreparedStatement pstmt = con.prepareStatement(sql);
	    pstmt.setInt(1, length);
	    pstmt.setString(2, x);
	    pstmt.setInt(3, 3);
	    pstmt.setInt(4, 4);
	    pstmt.executeUpdate();
	    pstmt.close();
	}
	reader.close();
	
	
	
    }
    
    public void insertBookData(ArrayList<GutFile> g) throws SQLException, IOException
    {
	Statement stmt = con.createStatement();
        String query = "DELETE FROM books";
        int deletedRows=stmt.executeUpdate(query);

	for(GutFile f : g)
	{
	    String sql = "INSERT INTO books (title,author) VALUES(?,?)";  
	    PreparedStatement pstmt = con.prepareStatement(sql);
	    pstmt.setString(1, f.getTitle());
	    pstmt.setString(2, f.getAuthor());
	    pstmt.executeUpdate();
	    pstmt.close();
	}	
    }
    public void close() throws SQLException
    {
	con.close();
    }
}
